<?php

/**
 * Project: CrazyPoems
 * Author: Alexandru
 * Date: 20/10/2008
 * File: functions.index.php
 */

// New poems.
$smarty->assign('newPoems', getNewPoems());

// Most readed poems.
// $smarty->assign('mostReadedPoems', getMostReadedPoems());

// New comments.
$smarty->assign('newComments', getNewComments());

// New members.
$smarty->assign('newMembers', getNewMembers());
// Site links 
$smarty->assign('siteLinks', getSiteLinks());

// Languages.
$smarty->assign('languages', getLanguages());

// Get rogrup ads.
if (LANG=='ro')
  $smarty->assign('rogrup_ads_poems_list', curl_get_file_contents("http://www.rogrup.com/share.php"));

// Categories and selected.
$smarty->assign('categories', getCategories($selectedCategory));

function getNewPoems() {
  $newPoems = array();
  $q = mysql_query("SELECT id,title,category FROM ".TABLE_POEMS." WHERE visible = 'Y' ORDER BY id DESC LIMIT 0,10") or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $tmp = array('id' => $r['id'], 'title' => $r['title'], 'link' => get_link('poem', array('id' => $r['id'], 'title' => $r['title'], 'category' => $r['category'])));
    $newPoems[] = $tmp;
  }
  return $newPoems;
}

function getNewComments() {
  $newComments = array();
  $q = mysql_query("SELECT id,poem_id,comment,author FROM ".TABLE_COMMENTS." ORDER BY id DESC LIMIT 0,6") or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $comment = $r['comment'];
    if (strlen($comment)>50) {
      $comment = substr($comment, 0, 50) . "..";
    }
    $tmp = array('id' => $r['id'],
            'poem_id' => $r['poem_id'],
            'author' => $r['author'],
            'comment' => $comment,
            'link' => get_link('poem', array('id' => $r['poem_id'], 'title' => getPoemById($r['poem_id']), 'category' => 0))
    );
    $newComments[] = $tmp;
  }
  return $newComments;
}

function getMostReadedPoems() {
  $mostReadedPoems = array();
  $q = mysql_query("SELECT id,title FROM ".TABLE_POEMS." WHERE visible = 'Y' ORDER BY readed DESC LIMIT 0,10") or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $tmp = array('id' => $r['id'], 'title' => $r['title']);
    $mostReadedPoems[] = $tmp;
  }
  return $mostReadedPoems;
}

function getNewMembers() {
  $newMembers = array();
  $q = mysql_query("SELECT id,username FROM ".TABLE_USERS." ORDER BY id DESC LIMIT 0,5") or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $tmp = array('id' => $r['id'], 'username' => $r['username']);
    $newMembers[] = $tmp;
  }
  return $newMembers;
}

function getAllMembers() {
  $result = array();
  $q = mysql_query("SELECT username, fullname, email FROM ".TABLE_USERS." ORDER BY username") or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $result[] = $r;
  }
  return $result;
}

function getSiteLinks() {
  $siteLinks = array();
  $q = mysql_query("SELECT * FROM ".TABLE_SITE_LINKS." WHERE lang = '".LANG."' ORDER BY RAND()") or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $tmp = array('url' => $r['url'], 'name' => $r['name'], 'description' => $r['description']);
    $siteLinks[] = $tmp;
  }
  return $siteLinks;
}

function getLanguages() {
  $result = array();
  $q = mysql_query("SELECT * FROM `language`") or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $result[$r['code']] = $r['name'];
  }
  return $result;
}

function getCategories($selected, $showHidden = false) {
  $categories = array();
  $name = LANG != 'es' ? 'name_'.LANG : 'name';
  if ($showHidden) {
    $c = "SELECT id, $name AS name FROM ".TABLE_CATEGORIES." ORDER BY $name";
  } else {
    $c = "SELECT id, $name AS name FROM ".TABLE_CATEGORIES." WHERE active = 1 ORDER BY $name";
  }
  $q = mysql_query($c) or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $isSelected = ($r['id'] == $selected) ? true : false;
    $tmp = array('id' => $r['id'], 'name' => $r['name'], 'selected' => $isSelected);
    $categories[] = $tmp;
    $isSelected  = "";
  }
  return $categories;
}

function getCategoryById($id) {
  $name = LANG != 'es' ? 'name_'.LANG : 'name';
  $q = mysql_query("SELECT $name FROM ".TABLE_CATEGORIES." WHERE id = $id") or die(mysql_error());
  $r = mysql_fetch_assoc($q);
  if ($r) {
    return $r[$name];
  } else {
    return "Unknown category";
  }
}

function getPoemById($id) {
  $q = mysql_query("SELECT title FROM ".TABLE_POEMS." WHERE id = $id") or die(mysql_error());
  $r = mysql_fetch_assoc($q);
  if ($r) {
    return $r['title'];
  } else {
    return "Unknown";
  }
}

function getPoems($category, $limit, $perpage, $search) {

  // No comments? :)
  global $smarty;
  $poems = array();
  $author = isset($_GET['author']) ? trim($_GET['author']) : '';

  $c = "SELECT * FROM ".TABLE_POEMS." WHERE visible = 'Y' AND lang = '".LANG."'";
  if (strlen($category) > 0) {
    $c .= " AND category = $category";
  }
  if (strlen($author) > 0) {
    $c .= " AND author = '".checkSQL($author)."'";
  }
  if (strlen($search) > 0) {
    $c .= " AND (title LIKE '%".checkSQL($search)."%' OR poem LIKE '%".checkSQL($search)."%')";
  }
  // order results
  $c .= " ORDER BY id DESC";
  if (strlen($perpage) > 0) {
    // get total results
    $q2 = mysql_query(str_replace("*","count(*) as numrows",$c)) or die(mysql_error());
    $r2 = mysql_fetch_assoc($q2);
    if ($r2) {
      $numrows = $r2['numrows'];
    }
    $maxPage = ceil($numrows/$perpage);
    // by default we show first page
    $pageNum = 1;
    // if $_GET['page'] defined, use it as page number
    if(isset($_GET['page'])) {
      $pageNum = $_GET['page'];
    }
    // counting the offset
    $offset = ($pageNum - 1) * $perpage;
    $c .= " LIMIT $offset, $perpage";
  } else {
    // Instead of using pagination show all results in one page and limit them to limit(IF SET)
    if (strlen($limit) > 0) {
      $c .= " LIMIT 0, $limit";
    }
  }
  $q = mysql_query($c) or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    if (strlen($r['poem']) > SHORT_POEM_SIZE) {
      $poem = substr($r['poem'], 0, SHORT_POEM_SIZE) . "..";
    } else {
      $poem = $r['poem'];
    }
    $tmp = array('id' => $r['id'], 'title' => $r['title'], 'poem' => nl2br($poem), 'author' => $r['author'], 'link' => get_link('poem', array('id' => $r['id'], 'title' => $r['title'], 'category' => $r['category'])));
    $poems[] = $tmp;
    unset($tmp);
  }
  if (strlen($perpage) > 0) {
    // pagination
    $nav  = "";
    $pageStart = $pageNum-3;
    if ($pageStart<1) {
      $pageStart = 1;
    }
    $pageEnd = $pageNum+3;
    if ($pageEnd>$maxPage) {
      $pageEnd = $maxPage;
    }
    for($page = $pageStart; $page <= $pageEnd; $page++) {
      if ($page == $pageNum) {
        $nav .= " $page "; // no need to create a link to current page
      } else {
        $nav .= " <a href=\"".urlWithout("page")."page=$page\">$page</a> ";
      }
    }
    if ($pageEnd<$maxPage) {
      $nav .= " .. <a href=\"".urlWithout("page")."page=$maxPage\">$maxPage</a> ";
    }
    if ($pageNum > 1) {
      $page  = $pageNum - 1;
      $prev  = " <a href=\"".urlWithout("page")."page=$page\">".l('Atrás')."</a> ";
      $first = " <a href=\"".urlWithout("page")."page=1\">".l('Primera Página')."</a> ";
    } else {
      $prev  = '&nbsp;'; // we're on page one, don't print previous link
      $first = '&nbsp;'; // nor the first page link
    }
    if ($pageNum < $maxPage) {
      $page = $pageNum + 1;
      $next = " <a href=\"".urlWithout("page")."page=$page\">".l('Adelante')."</a> ";

      $last = " <a href=\"".urlWithout("page")."page=$maxPage\">".l('Última Página')."</a> ";
    } else {
      $next = '&nbsp;'; // we're on the last page, don't print next link
      $last = '&nbsp;'; // nor the last page link
    }
    $smarty->assign('paging_nav', $nav);
    $smarty->assign('paging_first', $first);
    $smarty->assign('paging_prev', $prev);
    $smarty->assign('paging_next', $next);
    $smarty->assign('paging_last', $last);
  }
  return $poems;
}

function exportPoems() {

  $category = isset($_GET['category']) ? intval($_GET['category']) : '';
  $limit = 0;
  $search = isset($_GET['search']) ? $_GET['search'] : '';
  $author = isset($_GET['author']) ? trim($_GET['author']) : '';

  // No comments? :)
  $poems = array();

  $c = "SELECT * FROM ".TABLE_POEMS." WHERE visible = 'Y'";
  if (strlen($category) > 0) {
    $c .= " AND category = $category";
  }
  if (strlen($author) > 0) {
    $c .= " AND author = '".checkSQL($author)."'";
  }
  if (strlen($search) > 0) {
    $c .= " AND (title LIKE '%".checkSQL($search)."%' OR poem LIKE '%".checkSQL($search)."%')";
  }
  // order results
  $c .= " ORDER BY id DESC";

  $q = mysql_query($c) or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $poem = $r['poem'];
    $tmp = array('id' => $r['id'], 'title' => $r['title'], 'poem' => nl2br($poem), 'author' => $r['author'], 'link' => get_link('poem', array('id' => $r['id'], 'title' => $r['title'], 'category' => $r['category'])));
    $poems[] = $tmp;
    unset($tmp);
  }
  return $poems;
}

function share_poems_data() {

  $category = isset($_GET['category']) ? intval($_GET['category']) : '';
  $limit = isset($_GET['limit']) ? intval($_GET['limit']) : 15;
  $search = isset($_GET['search']) ? $_GET['search'] : '';
  $author = isset($_GET['author']) ? trim($_GET['author']) : '';
  $lang = isset($_GET['lang']) ? trim($_GET['lang']) : '';

  // No comments? :)
  $poems = '';
  $titles = '';
  $links = '';

  $c = "SELECT id, title, category FROM ".TABLE_POEMS." WHERE visible = 'Y'";
  if (strlen($category) > 0) {
    $c .= " AND category = $category";
  }
  if (strlen($author) > 0) {
    $c .= " AND author = '".checkSQL($author)."'";
  }
  if (strlen($search) > 0) {
    $c .= " AND (title LIKE '%".checkSQL($search)."%' OR poem LIKE '%".checkSQL($search)."%')";
  }
  if (strlen($lang) > 0) {
  	$c .= " AND lang = '".checkSQL($lang)."'";
  }
  // order results
  $c .= " ORDER BY id DESC LIMIT 0,$limit";

  $q = mysql_query($c) or die(mysql_error());
  while ($r = mysql_fetch_assoc($q)) {
    $links .= ",'http://www.crazypoems.net".get_link('poem', array('id' => $r['id'], 'title' => $r['title'], 'category' => $r['category']))."'";
    $r['title'] = htmlentities($r['title'], ENT_QUOTES,'UTF-8');
    $titles .= ",'".str_replace("'","\'",$r['title'])."'";
  }
  $titles = 'var cp_titles = new Array('.substr($titles,1).');'."\n";
  $links = 'var cp_links = new Array('.substr($links,1).');'."\n";
  $poems = $titles . $links;
  return $poems;
}

?>
